/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLager;
import ModelLager.*;
import ControlLager.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Ronni
 */
public class DbSalesOrderP implements IFDbSalesOrderP {
    private Connection con;
    private String dbName;
    private CustomerPCTR customers;
    public DbSalesOrderP() {
        con = DbConnection2.getInstance().getDBcon();
        dbName = "SalesOrderP";    
        customers = new CustomerPCTR();
        
    }
    
    
    public int getNextOrderID()
    {
        PreparedStatement preStmnt = null;
        String query = null;
        ResultSet result = null;
        int nextInt =0;
        try{
            query = "select MAX(orderID) from SalesOrderP";
//        query = "SELECT MAX(orderID) FROM SalesOrderP";
        Statement stmt = DbConnection2.getInstance().getDBcon().createStatement();
        //preStmnt = con.prepareStatement(query);
        //result = preStmnt.executeQuery();
        result = stmt.executeQuery(query);
        
        if(result.next()){
        System.out.println("før returns");
        nextInt = result.getInt(1);
        nextInt++;
        }
        return nextInt; 
        
        }catch(Exception e)
        {
            System.out.println(e.getMessage());
        return 0;
        }
  
        
    }
    
    
    @Override
        public int deleteSalesOrder(SalesOrderP order)
    {
        // metoden returnerer rc = -1(rc er kort for return code), hvis ikke 'try løkken' 
        // går igennem og vil derfor returnere en fejl.
        int rc = -1;
        //opretter et tomt statement objekt
        PreparedStatement preStmnt = null;
        //opretter en string der indeholder querien der skal eksekveres
        String query = null;
        try{
                query = "DELETE FROM " +dbName+ " WHERE orderID = ?";
                preStmnt = con.prepareStatement(query);
                preStmnt.setInt(1, order.getOrderID());
                rc = preStmnt.executeUpdate();
                preStmnt.close();
        }catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Fejl: " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("Exception " + e.getMessage());
        }        
        return rc;
        
    }
    @Override
     public int insertSalesOrder(SalesOrderP order)
    {
        // metoden returnerer rc = -1(rc er kort for return code), hvis ikke 'try løkken' 
        // går igennem og vil derfor returnere en fejl.
        int rc = -1;
        //opretter et tomt statement objekt
        PreparedStatement preStmnt = null;
        //opretter en string der indeholder querien der skal eksekveres
        String query = null;
        
        //fordi con.prepareStatement metoden "kaster" en fejl fejl(SQLException)
        //skal koden indrammes i try/catch
        try
        {
                query = "INSERT INTO " +dbName+ " VALUES(?,?,?,?,?,?,?,?,null)";
                preStmnt = con.prepareStatement(query);
                System.out.println(query);
                System.out.println(order.getOrderID());
                preStmnt.setInt(1, order.getOrderID());
                java.sql.Date date = new java.sql.Date(1995,01,01);
                preStmnt.setDate(2, date);
                preStmnt.setDouble(3, order.getTotalPrice());
                java.sql.Date date2 = new java.sql.Date(1995,01,01);
                preStmnt.setDate(4, date2);
                preStmnt.setDouble(5, order.getVAT());
                preStmnt.setDouble(6, order.getTotalPriceNoVAT());
                preStmnt.setInt(7, order.getCustomer().getCustomerID());
                preStmnt.setInt(8, order.getDeliveryStatus());
                //preStmnt.setInt(9, order.getInVoice().getInVoiceNo());
                rc = preStmnt.executeUpdate();   
                
        }
        catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQLException in InsertSalesOrder: " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("Exception in InsertSalesOrder" + e.getMessage());
        }        
        return rc;

    }
    @Override
    public int updateSalesOrder(SalesOrderP order)
    {
        // metoden returnerer rc = -1(rc er kort for return code), hvis ikke 'try løkken' 
        // går igennem og vil derfor returnere en fejl.
        int rc = -1;
        //opretter et tomt statement objekt
        //opretter en string der indeholder querien der skal eksekveres
        String query = null;
        
        //fordi con.prepareStatement metoden "kaster" en fejl fejl(SQLException)
        //skal koden indrammes i try/catch
        try
        {
        
query = "UPDATE " +dbName+ " SET orderID = ?, date = ?,totalPrice = ?,deliveryDate = ?,vat = ?,"
        + "totalPriceNoVAT = ?,customerID = ?,deliveryStatus = ?, inVoiceNo = ? WHERE orderID = " + order.getOrderID();

PreparedStatement preStmnt = con.prepareStatement(query);
System.out.println(query);
                preStmnt = con.prepareStatement(query);
                preStmnt.setInt(1, order.getOrderID());
                java.sql.Date date = new java.sql.Date(order.getDate().getTime());
                preStmnt.setDate(2, date);
                preStmnt.setDouble(3, order.getTotalPrice());
                java.sql.Date date2 = new java.sql.Date(order.getDate().getTime());
                preStmnt.setDate(4, date2);
                preStmnt.setDouble(5, order.getVAT());
                preStmnt.setDouble(6, order.getTotalPriceNoVAT());
                preStmnt.setInt(7, order.getCustomer().getCustomerID());
                preStmnt.setInt(8, order.getDeliveryStatus());
                preStmnt.setInt(9, order.getInVoice().getInVoiceNo());
                rc = preStmnt.executeUpdate();   
                
        }
        catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Error: " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("ExceptionE: " + e.getMessage());
        }        
        return rc;

    }
    
    @Override
        public ArrayList<SalesOrderP> findAllSalesOrders()
    {      
            PreparedStatement preStmnt = null;          
            ArrayList<SalesOrderP> list = new ArrayList<SalesOrderP>();
            ResultSet results;
            SalesOrderP sObj = null;
            try{    
            String query = "SELECT * FROM " + dbName;     
            preStmnt = con.prepareStatement(query);
            results = preStmnt.executeQuery();
            while(results.next()){
                 sObj = buildOrder(results);
                 list.add(sObj);	
            }
            preStmnt.close();
            }catch(SQLException SQLe)
            {
                System.out.println("SQL Exception: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i findAllProduct(): " + e.getMessage());
                
            }
            
            return list;
        
    }    
    
    @Override
        public SalesOrderP findSalesOrder(int orderID)
    {      
            PreparedStatement preStmnt =  getPreparedStmnt();          
            SalesOrderP pObj = null;
            ResultSet results;
            try{    
            preStmnt.setInt(1,orderID);                
            results = preStmnt.executeQuery();
            if(results.next()){
                 pObj = buildOrder(results);	
            }
            }catch(SQLException SQLe)
            {
                System.out.println("SQL Exception: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i findProduct(): " + e.getMessage());
                
            }
            
            return pObj;
        
    }
    private PreparedStatement getPreparedStmnt()
    {
        PreparedStatement preStmnt = null;
        try{
        String query = "SELECT * FROM "+dbName+" WHERE orderID = ?";
        preStmnt = con.prepareStatement(query);
        }catch(Exception e)
        {
            System.out.println("Error in getpreparedstatement--> " + e.getMessage());
        }
        return preStmnt;
    }
    
    
        private SalesOrderP buildOrder(ResultSet results)
    {
       SalesOrderP sObj = new SalesOrderP();
       
         try{
                        
                sObj.setOrderID(results.getInt("orderID"));
                sObj.setDate(results.getDate("date"));
                sObj.setTotalPrice(results.getInt("totalPrice"));
                sObj.setDeliveryDate(results.getDate("deliveryDate"));
                sObj.setVAT(results.getDouble("vat"));
                sObj.setTotalPriceNoVAT(results.getDouble("totalPriceNoVat"));
                sObj.setCustomer(customers.findCustomerP(results.getInt("customerID")));
                sObj.setDeliveryStatus(results.getInt("deliveryStatus"));
                //sObj.setInVoice(results.getInt("inVoiceNo")); --> skal tilknyttes invoice
               
          }
         catch(Exception e)
         {
             System.out.println("error building project object: " + e.getMessage());
         }
        
        return sObj;
        
    }
    
    
}
